﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using MySql.Data.MySqlClient;

namespace TeamService.Access
{
    public class TeamServeiceAccessor
    {
        MySqlConnection _conn;
        public TeamServeiceAccessor()
        {
            MySqlConnectionStringBuilder build = new MySqlConnectionStringBuilder()
            {
                Server = "192.168.31.236",
                Port =3306,
                Database="teamservice",
                Pooling = true,
                Password ="p62347.12",
                UserID= "InoriAd"
            };
            try
            {
                _conn = new MySqlConnection(build.ConnectionString);
                _conn.Open();
            }
            catch
            {

            }
        }

        public bool AddMember(Member m)
        {
            var str = "insert into member (name,phone,qq,title)values(@name,@phone,@qq,@title)";
            MySqlParameter name = new MySqlParameter(nameof(m.Name), MySqlDbType.VarChar) { Value = m.Name };
            MySqlParameter phone = new MySqlParameter(nameof(m.Phone), MySqlDbType.VarChar){ Value = m.Phone};

            MySqlParameter qq = new MySqlParameter(nameof(m.QQ), MySqlDbType.VarChar) { Value = m.QQ};
            MySqlParameter title = new MySqlParameter(nameof(m.Title), MySqlDbType.VarChar) { Value = m.Title};
            using (var com = new MySqlCommand(str,_conn))
            {
                com.Parameters.Add(name);
                com.Parameters.Add(phone);
                com.Parameters.Add(qq);
                com.Parameters.Add(title);                
                try
                {
                    var code = com.ExecuteNonQuery();
                    return code == 1;
                }
                catch
                {
                    return false;
                }
                
            }
        }
        public List<Member> SelectMember()
        {
            List<Member> list =null;
            using (var com = new MySqlCommand(@"select * from member", _conn))
            {
                using (var result = com.ExecuteReader())
                {
                    list = new List<Member>();
                    while (result.Read())
                    {
                        if(result.HasRows)
                        {
                            var e = new Member();
                            e.Name = result.GetString("name");
                            e.Phone = result.GetString("phone");
                            e.QQ = result.GetString("qq");
                            e.Title = result.GetString("title");
                            e.Id = result.GetInt32("no");
                            list.Add(e);
                        }
                    }
                }
            }
            return list;
        }
        public bool RemoveMember(Member member)
        {
            var str = "delete from member where name=@name";
            MySqlParameter name = new MySqlParameter(nameof(member.Name), MySqlDbType.VarChar) { Value = member.Name};
            using (var com = new MySqlCommand(str, _conn))
            {
                com.Parameters.Add(name);
                return com.ExecuteNonQuery() == 1;
            }
        }

        public bool AddProject(Project p)
        {
            var str = "insert into project (name,describe,state)values(@name,@des,@state)";
            MySqlParameter name = new MySqlParameter("name", MySqlDbType.VarChar) { Value = p.Name};
            MySqlParameter des = new MySqlParameter("des", MySqlDbType.VarChar) { Value = p.Description};
            MySqlParameter state = new MySqlParameter("state", MySqlDbType.Int32) { Value = p.State};
            using (var com = new MySqlCommand(str, _conn))
            {
                com.Parameters.Add(name);
                com.Parameters.Add(des);
                com.Parameters.Add(state);
                if(com.ExecuteNonQuery()==1)
                {
                    return true;
                }
                
            }
            return false;
        }
        public bool RemoveProject(Project p)
        {
            var str = "delete from project where name =@name";
            MySqlParameter name = new MySqlParameter("name", MySqlDbType.VarChar) { Value = p.Name };
            using (var com = new MySqlCommand(str, _conn))
            {
                com.Parameters.Add(name);
                if (com.ExecuteNonQuery() == 1)
                {
                    return true;
                }

            }
            return false;
        }
        public List<Project> SelectProject()
        {
            List<Project> list = null;
            using (var com = new MySqlCommand(@"select * from project", _conn))
            {
                using (var result = com.ExecuteReader())
                {
                    list = new  List<Project>();
                    while (result.Read())
                    {
                        if (result.HasRows)
                        {
                            var e = new Project();
                            e.Description = result.GetString("description");
                            e.Name = result.GetString("name");
                            e.State = (ProjectState)result.GetInt32("state");
                            e.Id = result.GetInt32("no");
                            list.Add(e);
                        }
                    }
                }
            }
            return list;
        }

        public bool AddTeam(Team t)
        {
            var str = "insert into team (name,des) values (@name,@des)";
            MySqlParameter name = new MySqlParameter("name", MySqlDbType.VarChar) { Value = t.Name };
            MySqlParameter des = new MySqlParameter("des", MySqlDbType.VarChar) { Value = t.Description };
            bool b;
            using (var com = new MySqlCommand(str, _conn))
            {
                com.Parameters.Add(name);
                com.Parameters.Add(des);
                b = com.ExecuteNonQuery() == 1;
            }
            return b;
        }
        public bool RemoveTeam(Team t)
        {
            var str = "delete from team where name = @name";
            var p = new MySqlParameter("name", MySqlDbType.VarChar) { Value = t.Name };
            using (var com = new MySqlCommand(str, _conn))
            {
                com.Parameters.Add(p);
                var code = com.ExecuteNonQuery();
                return code == 1;
            }
        }
        public List<Team> SelectTeam()
        {
            List<Team> list = null;
            using (var com = new MySqlCommand(@"select * from team", _conn))
            {
                using (var result = com.ExecuteReader())
                {
                    list = new List<Team>();
                    while (result.Read())
                    {
                        if (result.HasRows)
                        {
                            var e = new Team();
                            e.Id = result.GetInt32("id");
                            e.Name = result.GetString("name");
                            list.Add(e);
                        }
                    }
                }
            }
            return list;
        }

        public bool JoinProject(Team t,Project p)
        {
            var str = "insert into ef_pt (project,team) values (@p,@t)";
            var team = new MySqlParameter("t", MySqlDbType.Int32) { Value = t.Id };
            var project = new MySqlParameter("p", MySqlDbType.Int32) { Value = p.Id };
            using (var com = new MySqlCommand(str, _conn))
            {
                com.Parameters.Add(team);
                com.Parameters.Add(project);
                var code = com.ExecuteNonQuery();
                return code == 1;
            }
        }
        public bool RemoveTeam(Team t,Project p)
        {
            var str = "delete from ef_pt where team =@t and project =@p";
            var team = new MySqlParameter("t", MySqlDbType.Int32) { Value = t.Id };
            var project = new MySqlParameter("p", MySqlDbType.Int32) { Value = p.Id };
            using (var com = new MySqlCommand(str, _conn))
            {
                com.Parameters.Add(team);
                com.Parameters.Add(project);
                var code = com.ExecuteNonQuery();
                return code == 1;
            }
        }

        public bool JoinTeam(Member m,Team t)
        {
            var str = "insert into ef_tm (team,no) values (@t,@m)";
            var team = new MySqlParameter("t", MySqlDbType.Int32) { Value = t.Id };
            var mem = new MySqlParameter("m", MySqlDbType.Int32) { Value = m.Id };
            using (var com = new MySqlCommand(str, _conn))
            {
                com.Parameters.Add(team);
                com.Parameters.Add(mem);
                var code = com.ExecuteNonQuery();
                return code == 1;
            }
        }
        public bool RemoveMember(Member m,Team t)
        {
            var str = "delete from ef_tm where team = @t and no = @m";
            var team = new MySqlParameter("t", MySqlDbType.Int32) { Value = t.Id };
            var mem = new MySqlParameter("m", MySqlDbType.Int32) { Value = m.Id };
            using (var com = new MySqlCommand(str, _conn))
            {
                com.Parameters.Add(team);
                com.Parameters.Add(mem);
                var code = com.ExecuteNonQuery();
                return code == 1;
            }
        }

        public int SelectRight(Member m)
        {
            Member l = null;
            using (var com = new MySqlCommand(@"select author from ef_right where no = @no", _conn))
            {
                var p = new MySqlParameter("no", MySqlDbType.Int32) { Value = m.Id };
                com.Parameters.Add(p);
                using (var result = com.ExecuteReader())
                {
                    l = new Member();
                    while (result.Read())
                    {
                        if (result.HasRows)
                        {
                            var e = new Member();
                            return result.GetInt32("author");
                        }
                    }
                }
            }
            return -1;
        }

        public List<Team> FindTeamInProject(Project p)
        {
            var str = "select * from team join ef_pt on ef_pt.project=@p";
            MySqlParameter param = new MySqlParameter("p", MySqlDbType.Int32) {Value = p.Id };
            List<Team> l =null;

            using (var com = new MySqlCommand(str, _conn))
            {
                com.Parameters.Add(param);
                using (var r = com.ExecuteReader())
                {
                    l = new List<Team>();
                    while(r.Read())
                    {
                        if(r.HasRows)
                        {
                            var e = new Team();
                            e.Id = r.GetInt32("id");
                            e.Name = r.GetString("name");
                            e.Description = r.GetString("des");
                            l.Add(e);
                        }
                    }
                }

            }
            return l;
        }
        public List<Member> FindMemberInTeam(Team p)
        {
            var str = "select * from member join ef_tm on ef_tm.team = @p";
            MySqlParameter param = new MySqlParameter("p", MySqlDbType.Int32) { Value = p.Id };
            List<Member> l = null;

            using (var com = new MySqlCommand(str, _conn))
            {
                com.Parameters.Add(param);
                using (var r = com.ExecuteReader())
                {
                    l = new List<Member>();
                    while (r.Read())
                    {
                        if (r.HasRows)
                        {
                            var e = new Member();
                            e.Id = r.GetInt32("no");
                            e.Name = r.GetString("name");
                            e.Phone = r.GetString("phone");
                            e.QQ = r.GetString("qq");
                            e.Title = r.GetString("title");
                            l.Add(e);
                        }
                    }
                }

            }
            return l;
        }

        public List<Project> FindProjectsByKey(string key)
        {
            var str = "select * from project where name like %@key%";
            MySqlParameter p = new MySqlParameter("key", MySqlDbType.VarChar) { Value = key };
            List<Project> l = null;
            using (var com = new MySqlCommand(str, _conn))
            {
                using(var r = com.ExecuteReader())
                {
                    l = new List<Project>();
                    while (r.Read())
                    {
                        if (r.HasRows)
                        {
                            var e = new Project();
                            e.Description = r.GetString("description");
                            e.Name = r.GetString("name");
                            e.State = (ProjectState)r.GetInt32("state");
                            e.Id = r.GetInt32("no");
                            l.Add(e);
                        }
                    }
                }
            }
            return l;
        }

        public List<Team> FindTeamsByKey(string key)
        {
            var str = "select * from Team where name like %@key%";
            MySqlParameter p = new MySqlParameter("key", MySqlDbType.VarChar) { Value = key };
            List<Team> l = null;
            using (var com = new MySqlCommand(str, _conn))
            {
                using (var r = com.ExecuteReader())
                {
                    l = new List<Team>();
                    while (r.Read())
                    {
                        if (r.HasRows)
                        {
                            var e = new Team();
                            e.Name = r.GetString("name");
                            e.Id = r.GetInt32("no");
                            l.Add(e);
                        }
                    }
                }
            }
            return l;
        }

        public List<Member> FindMemberByKey(string key)
        {
            var str = "select * from Member where name like %@key%";
            MySqlParameter p = new MySqlParameter("key", MySqlDbType.VarChar) { Value = key };
            List<Member> l = null;
            using (var com = new MySqlCommand(str, _conn))
            {
                using (var r = com.ExecuteReader())
                {
                    l = new List<Member>();
                    while (r.Read())
                    {
                        if (r.HasRows)
                        {
                            var e = new Member();
                            e.Name = r.GetString("name");
                            e.Phone = r.GetString("phone");
                            e.QQ = r.GetString("qq");
                            e.Title = r.GetString("title");
                            e.Id = r.GetInt32("no");
                            l.Add(e);
                        }
                    }
                }
            }
            return l;
        }
        public List<Member> FindMemberByPhone(string phone)
        {
            var str = "select * from Member where phone like %@key%";
            MySqlParameter p = new MySqlParameter("key", MySqlDbType.VarChar) { Value = phone };
            List<Member> l = null;
            using (var com = new MySqlCommand(str, _conn))
            {
                using (var r = com.ExecuteReader())
                {
                    l = new List<Member>();
                    while (r.Read())
                    {
                        if (r.HasRows)
                        {
                            var e = new Member();
                            e.Name = r.GetString("name");
                            e.Phone = r.GetString("phone");
                            e.QQ = r.GetString("qq");
                            e.Title = r.GetString("title");
                            e.Id = r.GetInt32("no");
                            l.Add(e);
                        }
                    }
                }
            }
            return l;
        }
        public List<Member> FindMemberByQQ(string phone)
        {
            var str = "select * from Member where qq like %@key%";
            MySqlParameter p = new MySqlParameter("key", MySqlDbType.VarChar) { Value = phone };
            List<Member> l = null;
            using (var com = new MySqlCommand(str, _conn))
            {
                using (var r = com.ExecuteReader())
                {
                    l = new List<Member>();
                    while (r.Read())
                    {
                        if (r.HasRows)
                        {
                            var e = new Member();
                            e.Name = r.GetString("name");
                            e.Phone = r.GetString("phone");
                            e.QQ = r.GetString("qq");
                            e.Title = r.GetString("title");
                            e.Id = r.GetInt32("no");
                            l.Add(e);
                        }
                    }
                }
            }
            return l;
        }
    }
}
